package models

import (
	"database/sql"
	"strings"
	"time"

	"golang.org/x/crypto/bcrypt"
)

// contains 检查字符串是否包含子串
func contains(s, substr string) bool {
	return strings.Contains(s, substr)
}

// Shuoshuo 说说模型
type Shuoshuo struct {
	ID          int       `json:"id" db:"id"`
	UserID      int       `json:"user_id" db:"user_id"`
	Content     string    `json:"content" db:"content"`
	CreatedAt   time.Time `json:"created_at" db:"created_at"`
	IsViolation bool      `json:"is_violation" db:"is_violation"`
	Nickname    string    `json:"nickname" db:"nickname"`
}

// User 用户模型
type User struct {
	ID        int       `json:"id" db:"id"`
	Username  string    `json:"username" db:"username"`
	Nickname  string    `json:"nickname" db:"nickname"`
	Password  string    `json:"password" db:"password"`
	IsBanned  bool      `json:"is_banned" db:"is_banned"`
	CreatedAt time.Time `json:"created_at" db:"created_at"`
}

// Comment 评论模型
type Comment struct {
	ID         int       `json:"id" db:"id"`
	ShuoshuoID int       `json:"shuoshuo_id" db:"shuoshuo_id"`
	UserID     int       `json:"user_id" db:"user_id"`
	Content    string    `json:"content" db:"content"`
	CreatedAt  time.Time `json:"created_at" db:"created_at"`
	Nickname   string    `json:"nickname" db:"nickname"`
}

// Admin 管理员模型
type Admin struct {
	ID       int    `json:"id" db:"id"`
	Username string `json:"username" db:"username"`
	Password string `json:"password" db:"password"`
}

// InitDB 初始化数据库
func InitDB() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", "./saysay.db")
	if err != nil {
		return nil, err
	}

	// 创建用户表
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS users (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			username TEXT UNIQUE NOT NULL,
			nickname TEXT NOT NULL,
			password TEXT NOT NULL,
			is_banned BOOLEAN DEFAULT 0,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP
		)
	`)
	if err != nil {
		return nil, err
	}

	// 如果表已存在但没有is_banned字段，添加该字段
	_, err = db.Exec("ALTER TABLE users ADD COLUMN is_banned BOOLEAN DEFAULT 0")
	if err != nil && !contains(err.Error(), "duplicate column") {
		// 忽略"列已存在"的错误
	}

	// 创建说说表
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS shuoshuos (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_id INTEGER NOT NULL,
			content TEXT NOT NULL,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			is_violation BOOLEAN DEFAULT 0,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		return nil, err
	}

	// 创建评论表
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS comments (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			shuoshuo_id INTEGER NOT NULL,
			user_id INTEGER NOT NULL,
			content TEXT NOT NULL,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (shuoshuo_id) REFERENCES shuoshuos(id) ON DELETE CASCADE,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		)
	`)
	if err != nil {
		return nil, err
	}

	// 创建管理员表
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS admins (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			username TEXT UNIQUE NOT NULL,
			password TEXT NOT NULL
		)
	`)
	if err != nil {
		return nil, err
	}

	// 检查并创建默认管理员
	var count int
	err = db.QueryRow("SELECT COUNT(*) FROM admins").Scan(&count)
	if err != nil {
		return nil, err
	}

	if count == 0 {
		hashedPassword, _ := bcrypt.GenerateFromPassword([]byte("admin123"), bcrypt.DefaultCost)
		_, err = db.Exec("INSERT INTO admins (username, password) VALUES (?, ?)", "admin", string(hashedPassword))
		if err != nil {
			return nil, err
		}
	}

	return db, nil
}

// GetAllShuoshuos 获取所有说说（保留兼容）
func GetAllShuoshuos(db *sql.DB) ([]Shuoshuo, error) {
	return GetAllShuoshuosWithUser(db)
}

// GetNormalShuoshuos 获取正常说说（保留兼容）
func GetNormalShuoshuos(db *sql.DB) ([]Shuoshuo, error) {
	return GetNormalShuoshuosWithUser(db)
}

// CreateShuoshuo 创建说说（保留兼容）
func CreateShuoshuo(db *sql.DB, content string) error {
	// 兼容旧版本，在没有user_id的情况下创建
	_, err := db.Exec("INSERT INTO shuoshuos (user_id, content) VALUES (?, ?)", 0, content)
	return err
}

// ToggleViolation 切换违规状态
func ToggleViolation(db *sql.DB, id int) error {
	_, err := db.Exec("UPDATE shuoshuos SET is_violation = NOT is_violation WHERE id = ?", id)
	return err
}

// CheckAdmin 验证管理员凭据
func CheckAdmin(db *sql.DB, username, password string) (bool, error) {
	var hashedPassword string
	err := db.QueryRow("SELECT password FROM admins WHERE username = ?", username).Scan(&hashedPassword)
	if err != nil {
		return false, err
	}

	err = bcrypt.CompareHashAndPassword([]byte(hashedPassword), []byte(password))
	return err == nil, nil
}

// GetAdminByUsername 根据用户名获取管理员信息
func GetAdminByUsername(db *sql.DB, username string) (*Admin, error) {
	var admin Admin
	err := db.QueryRow("SELECT id, username, password FROM admins WHERE username = ?", username).Scan(&admin.ID, &admin.Username, &admin.Password)
	if err != nil {
		return nil, err
	}
	return &admin, nil
}

// ========== 用户相关函数 ==========

// CreateUser 创建用户
func CreateUser(db *sql.DB, username, nickname, password string) error {
	hashedPassword, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
	if err != nil {
		return err
	}
	_, err = db.Exec("INSERT INTO users (username, nickname, password) VALUES (?, ?, ?)", username, nickname, string(hashedPassword))
	return err
}

// CheckUsernameExists 检查用户名是否存在
func CheckUsernameExists(db *sql.DB, username string) (bool, error) {
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM users WHERE username = ?", username).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// CheckUser 验证用户凭据
func CheckUser(db *sql.DB, username, password string) (*User, error) {
	var user User
	err := db.QueryRow("SELECT id, username, nickname, password, is_banned FROM users WHERE username = ?", username).Scan(&user.ID, &user.Username, &user.Nickname, &user.Password, &user.IsBanned)
	if err != nil {
		return nil, err
	}

	// 检查是否被拉黑
	if user.IsBanned {
		return nil, sql.ErrNoRows // 返回记录不存在错误
	}

	err = bcrypt.CompareHashAndPassword([]byte(user.Password), []byte(password))
	if err != nil {
		return nil, err
	}

	return &user, nil
}

// GetUserByID 根据ID获取用户信息
func GetUserByID(db *sql.DB, userID int) (*User, error) {
	var user User
	err := db.QueryRow("SELECT id, username, nickname, is_banned FROM users WHERE id = ?", userID).Scan(&user.ID, &user.Username, &user.Nickname, &user.IsBanned)
	if err != nil {
		return nil, err
	}
	return &user, nil
}

// ========== 说说相关函数（更新）==========

// GetAllShuoshuosWithUser 获取所有说说（含用户信息）
func GetAllShuoshuosWithUser(db *sql.DB) ([]Shuoshuo, error) {
	rows, err := db.Query(`
		SELECT s.id, s.user_id, s.content, s.created_at, s.is_violation, u.nickname
		FROM shuoshuos s
		LEFT JOIN users u ON s.user_id = u.id
		ORDER BY s.created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var shuoshuos []Shuoshuo
	for rows.Next() {
		var s Shuoshuo
		var nickname sql.NullString

		err := rows.Scan(&s.ID, &s.UserID, &s.Content, &s.CreatedAt, &s.IsViolation, &nickname)
		if err != nil {
			return nil, err
		}

		// 处理可能为NULL的nickname
		if nickname.Valid {
			s.Nickname = nickname.String
		} else {
			s.Nickname = "匿名用户"
		}

		shuoshuos = append(shuoshuos, s)
	}

	return shuoshuos, nil
}

// GetNormalShuoshuosWithUser 获取正常说说（含用户信息）
func GetNormalShuoshuosWithUser(db *sql.DB) ([]Shuoshuo, error) {
	return GetNormalShuoshuosWithUserPaged(db, 0, 20)
}

// GetNormalShuoshuosWithUserPaged 分页获取正常说说（含用户信息）
func GetNormalShuoshuosWithUserPaged(db *sql.DB, offset, limit int) ([]Shuoshuo, error) {
	rows, err := db.Query(`
		SELECT s.id, s.user_id, s.content, s.created_at, s.is_violation, u.nickname
		FROM shuoshuos s
		LEFT JOIN users u ON s.user_id = u.id
		WHERE s.is_violation = 0
		ORDER BY s.created_at DESC
		LIMIT ? OFFSET ?
	`, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var shuoshuos []Shuoshuo
	for rows.Next() {
		var s Shuoshuo
		var nickname sql.NullString

		err := rows.Scan(&s.ID, &s.UserID, &s.Content, &s.CreatedAt, &s.IsViolation, &nickname)
		if err != nil {
			return nil, err
		}

		// 处理可能为NULL的nickname
		if nickname.Valid {
			s.Nickname = nickname.String
		} else {
			s.Nickname = "匿名用户"
		}

		shuoshuos = append(shuoshuos, s)
	}

	return shuoshuos, nil
}

// GetTotalShuoshuosCount 获取说说总数
func GetTotalShuoshuosCount(db *sql.DB) (int, error) {
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM shuoshuos WHERE is_violation = 0").Scan(&count)
	return count, err
}

// CreateShuoshuo 创建说说（更新，需要userID）
func CreateShuoshuoWithUser(db *sql.DB, userID int, content string) error {
	_, err := db.Exec("INSERT INTO shuoshuos (user_id, content) VALUES (?, ?)", userID, content)
	return err
}

// DeleteShuoshuo 删除说说
func DeleteShuoshuo(db *sql.DB, id, userID int) error {
	_, err := db.Exec("DELETE FROM shuoshuos WHERE id = ? AND user_id = ?", id, userID)
	return err
}

// GetShuoshuoByID 根据ID获取说说
func GetShuoshuoByID(db *sql.DB, id int) (*Shuoshuo, error) {
	var s Shuoshuo
	err := db.QueryRow("SELECT id, user_id, content, created_at, is_violation FROM shuoshuos WHERE id = ?", id).Scan(&s.ID, &s.UserID, &s.Content, &s.CreatedAt, &s.IsViolation)
	if err != nil {
		return nil, err
	}
	return &s, nil
}

// ========== 评论相关函数 ==========

// CreateComment 创建评论
func CreateComment(db *sql.DB, shuoshuoID, userID int, content string) error {
	_, err := db.Exec("INSERT INTO comments (shuoshuo_id, user_id, content) VALUES (?, ?, ?)", shuoshuoID, userID, content)
	return err
}

// GetCommentsByShuoshuoID 获取说说的所有评论（保留兼容）
func GetCommentsByShuoshuoID(db *sql.DB, shuoshuoID int) ([]Comment, error) {
	return GetCommentsByShuoshuoIDPaged(db, shuoshuoID, 0, 1)
}

// GetCommentsByShuoshuoIDPaged 分页获取说说评论
func GetCommentsByShuoshuoIDPaged(db *sql.DB, shuoshuoID, offset, limit int) ([]Comment, error) {
	rows, err := db.Query(`
		SELECT c.id, c.shuoshuo_id, c.user_id, c.content, c.created_at, u.nickname
		FROM comments c
		LEFT JOIN users u ON c.user_id = u.id
		WHERE c.shuoshuo_id = ?
		ORDER BY c.created_at ASC
		LIMIT ? OFFSET ?
	`, shuoshuoID, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var comments []Comment
	for rows.Next() {
		var c Comment
		err := rows.Scan(&c.ID, &c.ShuoshuoID, &c.UserID, &c.Content, &c.CreatedAt, &c.Nickname)
		if err != nil {
			return nil, err
		}
		comments = append(comments, c)
	}

	return comments, nil
}

// GetCommentCount 获取评论总数
func GetCommentCount(db *sql.DB, shuoshuoID int) (int, error) {
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM comments WHERE shuoshuo_id = ?", shuoshuoID).Scan(&count)
	return count, err
}

// ========== 管理员相关函数 ==========

// GetAllUsers 获取所有用户
func GetAllUsers(db *sql.DB) ([]User, error) {
	rows, err := db.Query("SELECT id, username, nickname, created_at, is_banned FROM users ORDER BY created_at DESC")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		err := rows.Scan(&u.ID, &u.Username, &u.Nickname, &u.CreatedAt, &u.IsBanned)
		if err != nil {
			return nil, err
		}
		users = append(users, u)
	}

	return users, nil
}

// BanUser 拉黑/取消拉黑用户
func BanUser(db *sql.DB, userID int) error {
	_, err := db.Exec("UPDATE users SET is_banned = NOT is_banned WHERE id = ?", userID)
	return err
}

// DeleteUser 删除用户
func DeleteUser(db *sql.DB, userID int) error {
	// 同时删除该用户的所有说说和评论（级联删除已在数据库定义）
	_, err := db.Exec("DELETE FROM users WHERE id = ?", userID)
	return err
}

// AdminDeleteShuoshuo 管理员删除说说
func AdminDeleteShuoshuo(db *sql.DB, shuoshuoID int) error {
	_, err := db.Exec("DELETE FROM shuoshuos WHERE id = ?", shuoshuoID)
	return err
}
